package com.x.erp.dao;

import java.util.ArrayList;
import java.util.List;

import com.x.erp.model.Market;
import com.x.erp.model.NoticeMarket;
import org.springframework.stereotype.Repository;

import com.x.erp.model.Notice;
import com.x.framework.dao.BaseDao;


@Repository
public class NoticeDao extends BaseDao {

    private final static String selectNoticeListSql = "SELECT T1.* FROM CMS_NOTICE T1 WHERE 1= 1 ";

    public List<Notice> selectNoticeList(Notice notice) throws Exception {
        StringBuilder sql = new StringBuilder(selectNoticeListSql);
        List<Object> params = new ArrayList<Object>();
        selectSearch(sql, params, notice);
        sql.append("ORDER BY NOTICE_TOP DESC, NOTICE_CREATE_TIME DESC ");
        return this.queryForList(sql.toString(), Notice.class, params, notice);
    }

	// 通过noticeId查找有几个市场
	private final static String selectNoticeListNumSql = "SELECT COUNT(T1.MARKET_ID) FROM CMS_NOTICE_MARKET T1 WHERE 1= 1 ";
	public int selectNoticeListNum(NoticeMarket noticeMarket) throws Exception {
		StringBuilder sql = new StringBuilder(selectNoticeListNumSql);
		List<Object> params = new ArrayList<Object>();
		if (this.isNotNull(noticeMarket.getNoticeId())) {
			sql.append("AND T1.NOTICE_ID = ? ");
			params.add(noticeMarket.getNoticeId());
		}
		return this.queryForInt(sql.toString(),params);
	}

    private final static String selectNoticeNumSql = "SELECT COUNT(T1.NOTICE_ID)  FROM CMS_NOTICE T1 WHERE 1 = 1 ";

    public int selectNoticeNum(Notice notice) throws Exception {
        StringBuilder sql = new StringBuilder(selectNoticeNumSql);
        List<Object> params = new ArrayList<Object>();
        selectSearch(sql, params, notice);
        return this.queryForInt(sql.toString(), params);
    }

    private final static String selectNoticeMarketListSql = "SELECT "
    		+ "T1.*, "
    		+ "T2.MARKET_ID "
    		+ "FROM "
    		+ "CMS_NOTICE T1, "
    		+ "CMS_NOTICE_MARKET T2, "
			+ "ERP_MARKET T3, "
			+ "ERP_MARKET T4 "
    		+ "WHERE "
    		+ "T1.NOTICE_ID = T2.NOTICE_ID "
    		+ "AND T2.MARKET_ID = T3.MARKET_ID "
    		+ "AND T4.MARKET_ID = ? AND T3.MARKET_CODE LIKE CONCAT(T4.MARKET_CODE, '%') ";

    public List<NoticeMarket> selectNoticeMarketList(NoticeMarket noticeMarket) throws Exception {
        StringBuilder sql = new StringBuilder(selectNoticeMarketListSql);
        List<Object> params = new ArrayList<Object>();
        params.add(noticeMarket.getMarketId());
        if (this.isNotNull(noticeMarket.getNoticeId())) {
            sql.append("AND T1.NOTICE_ID = ? ");
            params.add(noticeMarket.getNoticeId());
        }
        if (noticeMarket.getNotice() != null){
	        if (this.isNotNull(noticeMarket.getNotice().getNoticeState())) {
		        sql.append("AND T1.NOTICE_STATE = ? ");
		        params.add(noticeMarket.getNotice().getNoticeState());
	        }
	        if (noticeMarket.getNotice().getNoticeTop()!=null) {
		        sql.append("AND T1.NOTICE_TOP = ? ");
		        params.add(noticeMarket.getNotice().getNoticeTop());
	        }
            if(noticeMarket.getNotice().getNoticeStartTime() != null) {
                sql.append("AND ? >= T1.NOTICE_START_TIME ");
                sql.append("AND ? <= T1.NOTICE_END_TIME ");
                params.add(noticeMarket.getNotice().getNoticeStartTime());
                params.add(noticeMarket.getNotice().getNoticeStartTime());
            }
        }
        sql.append("GROUP BY T1.NOTICE_ID  ORDER BY T1.NOTICE_TOP DESC, T1.NOTICE_ID DESC ");
        return this.queryForList(sql.toString(), NoticeMarket.class, params, noticeMarket);
    }

    private final static String selectNoticeMarketNumSql = "SELECT "
    		+ "COUNT(DISTINCT(T1.NOTICE_ID)) "
    		+ "FROM "
    		+ "CMS_NOTICE T1, "
    		+ "CMS_NOTICE_MARKET T2, "
			+ "ERP_MARKET T3, "
			+ "ERP_MARKET T4 "
    		+ "WHERE "
    		+ "T1.NOTICE_ID = T2.NOTICE_ID "
    		+ "AND T2.MARKET_ID = T3.MARKET_ID "
    		+ "AND T4.MARKET_ID = ? AND T3.MARKET_CODE LIKE CONCAT(T4.MARKET_CODE, '%') ";

    public int selectNoticeMarketNum(NoticeMarket noticeMarket) throws Exception {
        StringBuilder sql = new StringBuilder(selectNoticeMarketNumSql);
        List<Object> params = new ArrayList<Object>();
        params.add(noticeMarket.getMarketId());
        return this.queryForInt(sql.toString(), params);
    }

	private final static String insertNoticeMarketListSql = "INSERT INTO CMS_NOTICE_MARKET VALUES ";

	/**
	 * 批量添加车辆图片信息
	 * @param notice
	 * @param selectMarketList
	 * @return
	 * @throws Exception
	 */
	public void insertNoticeMarketList(Notice notice, List<Market> selectMarketList) throws Exception {
		StringBuilder sql = new StringBuilder(insertNoticeMarketListSql);
		List<Object> params = new ArrayList<>();
		if (selectMarketList == null || selectMarketList.size() ==0){
			return;
		} else {
			for (int i = 0;i < selectMarketList.size();i++){
				if (i == 0 ){
					sql.append("(?,?) ");
				} else {
					sql.append(",(?,?) ");
				}
				params.add(notice.getNoticeId());
				params.add(selectMarketList.get(i).getMarketId());

			}
			this.execute(sql.toString(), params);
		}
	}



    private void selectSearch(StringBuilder sql, List<Object> params, Notice notice) {
        if (this.isNotNull(notice.getNoticeId())) {
            sql.append("AND T1.NOTICE_ID = ? ");
            params.add(notice.getNoticeId());
	        if(notice.getNoticeState() != null) {
		        sql.append("AND T1.NOTICE_STATE = ? ");
		        params.add(notice.getNoticeState());
	        }
	        if(notice.getNoticeTop() != null) {
		        sql.append("AND T1.NOTICE_TOP = ? ");
		        params.add(notice.getNoticeTop());
	        }
        }
    }

}
